--region [dbo].[AddGroup]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddGroup]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddGroup]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddGroup]
GO

CREATE PROCEDURE [dbo].[AddGroup]
	@GroupName nvarchar(50),
	@GroupType int,
	@GroupTypeName nvarchar(50),
	@DiseaseId int,
	@TreatmentId int,
	@SymptomId int,
	@LabcheckId int,
	@Area nvarchar(50),
	@ProvinceId int,
	@CityId int,
	@ProvinceName nvarchar(50),
	@CityName nvarchar(50),
	@Keyword nvarchar(500),
	@Introduction nvarchar(max),
	@Logo nvarchar(255),
	@Moderators nvarchar(255),
	@AppUserId int,
	@AppUserName nvarchar(50),
	@UserNum int,
	@TopicNum int,
	@ReplyNum int,
	@PostNum int,
	@LimitUser int,
	@LastPostTime datetime,
	@IsGoodGroup int,
	@Hits int,
	@CreatTime datetime,
	@BrowserPermission int,
	@JoinPermission int,
    @IsValid int
AS
DECLARE @Id int

BEGIN TRAN  
SET XACT_ABORT ON 

INSERT INTO [Group] (
	[GroupName],
	[GroupType],
	[GroupTypeName],
	[DiseaseId],
	[TreatmentId],
	[SymptomId],
	[LabcheckId],
	[Area],
	[ProvinceId],
	[CityId],
	[ProvinceName],
	[CityName],
	[Keyword],
	[Introduction],
	[Logo],
	[AppUserId],
	[AppUserName],
	[UserNum],
	[TopicNum],
	[ReplyNum],
	[PostNum],
	[LimitUser],
	[LastPostTime],
	[IsGoodGroup],
	[Hits],
	[CreatTime],
	[BrowserPermission],
	[JoinPermission],
    [IsValid]
) VALUES (
	@GroupName,
	@GroupType,
	@GroupTypeName,
	@DiseaseId,
	@TreatmentId,
	@SymptomId,
	@LabcheckId,
	@Area,
	@ProvinceId,
	@CityId,
	@ProvinceName,
	@CityName,
	@Keyword,
	@Introduction,
	@Logo,
	@AppUserId,
	@AppUserName,
	@UserNum,
	@TopicNum,
	@ReplyNum,
	@PostNum,
	@LimitUser,
	@LastPostTime,
	@IsGoodGroup,
	@Hits,
	@CreatTime,
	@BrowserPermission,
	@JoinPermission,
    @IsValid
)
SELECT SCOPE_IDENTITY()

SET @id = SCOPE_IDENTITY()



IF @GroupType = 0
BEGIN
	INSERT INTO [GroupUser](
		[GroupId],
		[UserId],
		[UserName],
		[Role],
		[TopicCount],
		[ReplyCount],
		[PostCount],
		[JoinTime]
	) VALUES ( @id,@AppUserId,@AppUserName,2,0,0,0,@CreatTime)
END
COMMIT TRAN  

GO
--endregion

--region [dbo].[AddGroupInvite]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddGroupInvite]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddGroupInvite]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddGroupInvite]
GO

CREATE PROCEDURE [dbo].[AddGroupInvite]
	@GroupId int,
	@GroupName nvarchar(50),
	@PostUserId int,
	@PostUserName nvarchar(50),
	@ReceiveUserId int,
	@PostTime datetime,
	@IsView int
AS
INSERT INTO [GroupInvite] (
      [GroupId],
      [GroupName],
      [PostUserId],
      [PostUserName],
      [ReceiveUserId],
      [PostTime],
      [IsView]

) VALUES (
    @GroupId,
    @GroupName,
	@PostUserId,
	@PostUserName,
	@ReceiveUserId,
	@PostTime,
	@IsView
	
)

GO
--endregion


--region [dbo].[DelGroupInvite]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelGroupInvite]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelGroupInvite]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelGroupInvite]
GO

CREATE PROCEDURE [dbo].[DelGroupInvite]
	@Id int
AS
DELETE FROM
	[GroupInvite]
WHERE
    [Id] = @Id

GO
--endregion


--region [dbo].[GetNewGroupInviteList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetGroupInviteList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetGroupInviteList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetGroupInviteList]
GO

CREATE PROCEDURE [dbo].[GetGroupInviteList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL

AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT([GroupInvite].[Id]) 
FROM
	[GroupInvite]
WHERE
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT TOP ' + STR(@PageSize) + '
	[Id],
	[PostUserId],
	[PostUserName],
	[PostTime],
	[GroupId],
	[GroupName]
FROM
	[GroupInvite]
WHERE
	'

IF @PageIndex > 1
BEGIN
SET @SQL = @SQL + '
	AND [GroupInvite].[Id] < 
	( 
	SELECT MIN([GroupInvite].[Id])  
	FROM ( 
	SELECT TOP 
		' + STR((@PageIndex-1)*@PageSize) + ' [GroupInvite].[Id] 
	FROM [GroupInvite]
	WHERE
	' 

	IF @WhereCondition <> ''
	BEGIN
		SET @SQL = @SQL + @WhereCondition
	END

	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression + ') AS tblTmp ) '
	END
	ELSE
	BEGIN
		SET @SQL = @SQL + ') AS tblTmp ) '
	END

	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression 
	END	
END
ELSE 
BEGIN
	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + ' 
	ORDER BY
		' + @OrderByExpression 
	END	
END

EXEC sp_executesql @SQL

GO

--endregion



--region [dbo].[UpdateGroup]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateGroup]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateGroup]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateGroup]
GO

CREATE PROCEDURE [dbo].[UpdateGroup]
	@Id int,
	@GroupName nvarchar(50),
	@GroupType int,
	@GroupTypeName nvarchar(50),
	@DiseaseId int,
	@TreatmentId int,
	@SymptomId int,
	@LabcheckId int,
	@Area nvarchar(50),
	@Keyword nvarchar(500),
	@Introduction nvarchar(max),
	@Logo nvarchar(255),
	@Moderators nvarchar(500),
	@AppUserId int,
	@AppUserName nvarchar(50),
	@LimitUser int,
	@BrowserPermission int,
	@JoinPermission int
AS

UPDATE [Group] SET
	[GroupName] = @GroupName,
	[GroupType] = @GroupType,
	[GroupTypeName] = @GroupTypeName,
	[DiseaseId] = @DiseaseId,
	[TreatmentId] = @TreatmentId,
	[SymptomId] = @SymptomId,
	[LabcheckId] = @LabcheckId,
	[Area] = @Area,
	[Keyword] = @Keyword,
	[Introduction] = @Introduction,
	[Logo] = @Logo,
	[Moderators] = @Moderators,
	[AppUserId] = @AppUserId,
	[AppUserName] = @AppUserName,
	[LimitUser] = @LimitUser,
	[BrowserPermission] = @BrowserPermission,
	[JoinPermission] = @JoinPermission
WHERE
	[Id] = @Id

GO
--endregion


--region [dbo].[IncreaseGroupHitNum]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[IncreaseGroupHitNum]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[IncreaseGroupHitNum]') IS NOT NULL
	DROP PROCEDURE [dbo].[IncreaseGroupHitNum]
GO

CREATE PROCEDURE [dbo].[IncreaseGroupHitNum]
	@Id int
AS

UPDATE [Group] SET
	[Hits] = [Hits] + 1
WHERE
	[Id] = @Id

GO
--endregion


--region [dbo].[UpdateGroupTopicNum]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateGroupTopicNum]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateGroupTopicNum]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateGroupTopicNum]
GO

CREATE PROCEDURE [dbo].[UpdateGroupTopicNum]
	@Id int,
	@Num int
AS

UPDATE [Group] SET
	[TopicNum] = [TopicNum] + @Num,
	[PostNum] = [PostNum] + @Num
WHERE
	[Id] = @Id

GO
--endregion

--region [dbo].[UpdateGroupReplyNum]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateGroupReplyNum]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateGroupReplyNum]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateGroupReplyNum]
GO

CREATE PROCEDURE [dbo].[UpdateGroupReplyNum]
	@Id int,
	@Num int
AS

UPDATE [Group] SET
	[ReplyNum] = [ReplyNum] + @Num,
	[PostNum] = [PostNum] + @Num
WHERE
	[Id] = @Id

GO
--endregion

--region [dbo].[UpdateGroupUserNum]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateGroupUserNum]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateGroupUserNum]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateGroupUserNum]
GO

CREATE PROCEDURE [dbo].[UpdateGroupUserNum]
	@Id int,
	@Num int
AS

UPDATE [Group] SET
	[UserNum] = [UserNum] + @Num
WHERE
	[Id] = @Id

GO
--endregion

--region [dbo].[DelGroup]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelGroup]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelGroup]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelGroup]
GO

CREATE PROCEDURE [dbo].[DelGroup]
	@condstr nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
DELETE FROM
	[Group]
WHERE
	' +'[GroupType] > 0 AND'+ @condstr

EXEC sp_executesql @SQL

GO
--endregion

--region [dbo].[InvalidateGroup]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[InvalidateGroup]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[InvalidateGroup]') IS NOT NULL
	DROP PROCEDURE [dbo].[InvalidateGroup]
GO

CREATE PROCEDURE [dbo].[InvalidateGroup]
	@gid int
AS

UPDATE [Group] SET
       [Group].[IsValid] = 0	
WHERE  
    [Id] = @gid

GO
--endregion


--region [dbo].[ChangeLatestTopic]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[ChangeLatestTopic]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[ChangeLatestTopic]') IS NOT NULL
	DROP PROCEDURE [dbo].[ChangeLatestTopic]
GO

CREATE PROCEDURE [dbo].[ChangeLatestTopic]
	@Id int,
	@LastPostUser  int,
	@LastTopicTitle  nvarchar(255),
	@LastTopicId int,
	@LastPostTime   datetime,
	@Delta int
AS

UPDATE 
	[Group] 
SET
	[LastPostUser] = @LastPostUser,
	[LastTopicTitle] = @LastTopicTitle,
	[LastTopicId] = @LastTopicId,
	[LastPostTime] = @LastPostTime,
	[TopicNum] = [TopicNum] + @Delta,
	[PostNum] = [PostNum] + @Delta
WHERE
	[Id] = @Id 

GO
--endregion


--region [dbo].[GetGroup]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetGroup]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetGroup]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetGroup]
GO

CREATE PROCEDURE [dbo].[GetGroup]
	@condstr nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT
	[Id],
	[GroupName],
	[GroupType],
	[GroupTypeName],
	[DiseaseId],
	[TreatmentId],
	[SymptomId],
	[LabcheckId],
	[Area],
	[ProvinceId],
	[CityId],
	[ProvinceName],
	[CityName],
	[Keyword],
	[Introduction],
	[Logo],
	[Moderators],
	[AppUserId],
	[AppUserName],
	[UserNum],
	[TopicNum],
	[ReplyNum],
	[PostNum],
	[LimitUser],
	[LastPostTime],
	[IsGoodGroup],
	[Hits],
	[CreatTime],
	[BrowserPermission],
	[JoinPermission],
	[IsValid]
FROM
	[Group]
WHERE
	' + @condstr

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetGroupList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetGroupList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetGroupList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetGroupList]
GO

CREATE PROCEDURE [dbo].[GetGroupList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(Id) 
FROM [Group]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *
FROM
	( SELECT 
	[Id],
	[GroupName],
	[GroupType],
	[GroupTypeName],
	[DiseaseId],
	[TreatmentId],
	[SymptomId],
	[LabcheckId],
	[Area],
	[ProvinceId],
	[CityId],
	[ProvinceName],
	[CityName],
	[Keyword],
	[Introduction],
	[Logo],
	[Moderators],
	[AppUserId],
	[AppUserName],
	[UserNum],
	[TopicNum],
	[ReplyNum],
	[PostNum],
	[LimitUser],
	[LastPostUser],
	[LastTopicId],
	[LastTopicTitle],
	[LastPostTime],
	[IsGoodGroup],
	[Hits],
	[CreatTime],
	[BrowserPermission],
	[JoinPermission],
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
FROM
	[Group]
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetTopGroups]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetTopGroups]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetTopGroups]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetTopGroups]
GO

CREATE PROCEDURE [dbo].[GetTopGroups]
	@N int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT TOP ' + STR(@N) + '
	[Id],
	[GroupName],
	[GroupType],
	[GroupTypeName],
	[DiseaseId],
	[TreatmentId],
	[SymptomId],
	[LabcheckId],
	[Area],
	[ProvinceId],
	[CityId],
	[ProvinceName],
	[CityName],
	[Keyword],
	[Introduction],
	[Logo],
	[Moderators],
	[AppUserId],
	[AppUserName],
	[UserNum],
	[TopicNum],
	[ReplyNum],
	[PostNum],
	[LimitUser],
	[LastPostTime],
	[IsGoodGroup],
	[Hits],
	[CreatTime],
	[BrowserPermission],
	[JoinPermission]
FROM
	[Group]
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END
	
IF @OrderByExpression <> ''
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression 
END	

EXEC sp_executesql @SQL

GO
--endregion

--region [dbo].[GetGroupsByUserId]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetGroupsByUserId]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetGroupsByUserId]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetGroupsByUserId]
GO

CREATE PROCEDURE [dbo].[GetGroupsByUserId]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250)
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT([GroupUser].[GroupId]) 
FROM 
	[Group], [GroupUser]
WHERE
	[Group].[Id] = [GroupUser].[GroupId] 
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT TOP ' + STR(@pagesize) + '
	[Group].[Id],
	[GroupName],
	[GroupType],
	[Area],
	[Introduction],
	[Logo],
	[Moderators],
	[AppUserId],
	[AppUserName],
	[UserNum],
	[TopicNum],
	[ReplyNum],
	[PostNum],
	[LimitUser],
	[LastPostTime],
	[IsGoodGroup],
	[Hits],
	[CreatTime],
	[BrowserPermission],
	[JoinPermission],
	[GroupUser].[Role]
FROM 
	[Group], [GroupUser]
WHERE
	[Group].[Id] = [GroupUser].[GroupId] 
	'

IF @PageIndex > 1
BEGIN
SET @SQL = @SQL + '
	AND [GroupId] < 
	( 
	SELECT MIN([GroupId])  
	FROM ( 
	SELECT TOP 
		' + STR((@PageIndex-1)*@PageSize) + ' [GroupId] 
	FROM [GroupUser] ' 

	IF @WhereCondition <> ''
	BEGIN
		SET @SQL = @SQL + ' WHERE ' + @WhereCondition
	END

	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression + ') AS tblTmp ) '
	END
	ELSE
	BEGIN
		SET @SQL = @SQL + ') AS tblTmp ) '
	END

	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression
	END	
END
ELSE 
BEGIN
	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + ' 
	ORDER BY
		' + @OrderByExpression 
	END	
END

EXEC sp_executesql @SQL

GO
--endregion
